# -*- coding: cp932 -*-
from win32com.client import constants, Dispatch

class ExcelWriter:
    xlApp = None
    xlWorkbook = None
    xlWorksheet = None
    def __init__(self, filename):
        self.xlApp = Dispatch('Excel.Application')
        try:
            self.xlWorkbook = self.xlApp.Workbooks.Open(filename)
            self.xlWorksheets = self.xlWorkbook.Worksheets
            self.xlActiveSheet = self.xlWorkbook.ActiveSheet
        except:
            raise 'Excel file open failed.'

    def IsSheetExists(self, sheet):
        try:
            self.xlWorksheets[sheet]
            return True
        except:
            return False

    def Save(self):
        self.xlWorkbook.Save()

    def Close(self):
        if self.xlWorkbook:
            self.xlWorkbook.Close(SaveChanges=0)
        del self.xlApp
        
    def SetValue(self, row, col, value=''):
        self.xlActiveSheet.Cells(row, col).Value = value
        
    def SelectSheet(self, sheet):
        self.xlActiveSheet = self.xlWorksheets[sheet]

    def AddSheet(self, sheetName=None, Before=None, After=None):
        BeforeSheet = Before and self.xlWorksheets[Before]
        AfterSheet = After and self.xlWorksheets[After]
        self.xlWorksheets.Add(BeforeSheet, AfterSheet)
        self.xlActiveSheet = self.xlApp.ActiveSheet
        if sheetName:
            self.xlActiveSheet.Name = sheetName

    def CopySheet(self, srcSheet, sheetName=None, Before=None):
##        if sheetName and sheetName in self.xlWorksheets:
##            self.SelectSheet(sheetName)
##            return
        if self.IsSheetExists(sheetName):
            self.SelectSheet(sheetName)
            return

        sheetSrc = self.xlWorksheets[srcSheet]
        if sheetSrc:
            BeforeSheet = Before and self.xlWorksheets[Before]
            sheetSrc.Copy(BeforeSheet)
            self.xlActiveSheet = self.xlApp.ActiveSheet
            if sheetName:
                self.xlActiveSheet.Name = sheetName

if __name__ == "__main__":
    writer = ExcelWriter(r'F:\instead\Systems\tool\test.xls')
    writer.SelectSheet(0)
    writer.SetValue(9, 'M', 'Write value')
    writer.AddSheet("test", 2)
    writer.CopySheet(0, "copy sheet", 2)
    #writer.Save()
    #writer.Close()
